package com.metagurukul.informer.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.metagurukul.informer.utility.ConnectionFactory;
import com.metagurukul.informer.valueObjects.Questions;

/**
 * Class searchDAO for providing the result for a search from the database
 * @author 
 *
 */
public class SearchDAO {
	   
   /**
    * ...Method to provide the list of questions related to the search string
    * @param searchString 
    * @return list of questions 
    */
   public List<Questions> findQuestions(String searchString) throws SQLException{
		PreparedStatement ps=null;
	   	ResultSet rs=null;
	    Connection con= ConnectionFactory.getInstance();
	   	List<Questions> questions=new ArrayList<Questions>();
		Questions ques=null;
	   	
   		/*
   		 * SQL query for finding out the questions related to the search string
   		 */
	   	 String sql = "SELECT * from questions where title LIKE ?"; 
		 ps = con.prepareStatement(sql);
		 ps.setString(1, searchString);
		 rs=ps.executeQuery(); 	//executing the query
		 
		 /*
		  * To make a question object for each tuple of the output
		  * adding that object to the output question list
		  */
		 while (rs.next()) {
				ques=new Questions(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getBoolean(6), rs.getString(4), rs
						.getDate(5));
				questions.add(ques);
	   	}
	   	
		 if(ps!=null){
		   	ps.close();
		}
		if(rs!=null) {
		   	rs.close();
		}
		   	
		if(con!=null){
			con.close();
		}				
		 
	   	return questions;
  }
	  
  /**
   * ...Method to provide the list of question related to a given tag
   * @param tagId
   * @return list of questions
   */
  public List<Questions> findQuestionsByTag(int tagId) throws SQLException {
	  PreparedStatement ps=null;
	  ResultSet rs=null;
	  Connection con= ConnectionFactory.getInstance();
	  List<Questions> questions=new ArrayList<Questions>();
	  Questions ques=null; 	
	  
		  /*
	   	   * SQL query for finding out the questions related to the tag
	   	   */
	   	 String sql = "SELECT * from questions " +
	   	 		"where id IN (SELECT qt.question_id " +
	   	 		"from question_tag_mapping qt " +
	   	 		"where qt.tag_id = ?)";
		 ps = con.prepareStatement(sql);
		 ps.setInt(1, tagId);
		 rs=ps.executeQuery();  //executing the query
		 
		 /*
		  * To make a question object for each tuple of the output
		  * adding that object to the output question list
		  */
		 while (rs.next()) {
				ques=new Questions(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getBoolean(6), rs.getString(4), rs
						.getDate(5));
				questions.add(ques);
	   	}
	   
		if(ps!=null){
			ps.close();
		}
		if(rs!=null) {
			rs.close();
		}
			   	
		if(con!=null){
			con.close();
		}	
		 
	  return questions;
  }
}
